import 'dotenv/config';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import { pool, query } from '../src/db/mysql.js';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

function usage() {
  console.log('Usage: node scripts/import_songs_from_json.js [jsonPath] [--truncate]');
  console.log('  jsonPath default: data/songs.json');
  process.exit(1);
}

async function main() {
  const args = process.argv.slice(2);
  let jsonPath = 'data/songs.json';
  let truncate = false;

  for (const a of args) {
    if (a === '--help' || a === '-h') usage();
    else if (a === '--truncate') truncate = true;
    else jsonPath = a;
  }

  const resolved = path.isAbsolute(jsonPath) ? jsonPath : path.resolve(path.join(__dirname, '..'), jsonPath);
  if (!fs.existsSync(resolved)) {
    console.error(`File not found: ${resolved}`);
    process.exit(2);
  }

  const raw = fs.readFileSync(resolved, 'utf-8');
  let items;
  try {
    items = JSON.parse(raw);
  } catch (e) {
    console.error('Invalid JSON:', e.message);
    process.exit(3);
  }
  if (!Array.isArray(items)) {
    console.error('JSON root must be an array of songs');
    process.exit(4);
  }

  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    if (truncate) {
      await conn.query('TRUNCATE TABLE songs');
      console.log('Truncated table songs');
    }

    let inserted = 0, updated = 0, skipped = 0;

    for (const s of items) {
      const {
        id = null,
        title,
        artist,
        album = null,
        cover = null,
        duration = null,
        url = null,
        lyrics = null
      } = s || {};

      if (!title || !artist) {
        skipped++;
        continue;
      }

      if (id != null && Number(id) > 0) {
        // Upsert by primary key id
        const sql = `
          INSERT INTO songs (id, title, artist, album, cover, duration, url, lyrics)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)
          ON DUPLICATE KEY UPDATE
            title = VALUES(title),
            artist = VALUES(artist),
            album = VALUES(album),
            cover = VALUES(cover),
            duration = VALUES(duration),
            url = VALUES(url),
            lyrics = VALUES(lyrics)
        `;
        const params = [Number(id), title, artist, album, cover, duration, url, lyrics];
        const [result] = await conn.query(sql, params);
        if (result.affectedRows === 1) inserted++;
        else if (result.affectedRows === 2) updated++; // insert on dup counts as 2
      } else {
        // Insert new row (auto-increment id)
        const sql = `
          INSERT INTO songs (title, artist, album, cover, duration, url, lyrics)
          VALUES (?, ?, ?, ?, ?, ?, ?)
        `;
        const params = [title, artist, album, cover, duration, url, lyrics];
        await conn.query(sql, params);
        inserted++;
      }
    }

    await conn.commit();
    console.log(`Done. inserted=${inserted}, updated=${updated}, skipped=${skipped}`);
  } catch (e) {
    await conn.rollback();
    console.error('Failed. Transaction rolled back.', e);
    process.exit(5);
  } finally {
    conn.release();
    await pool.end();
  }
}

main().catch(e => {
  console.error(e);
  process.exit(10);
});